package com.catal.framework.util;

import java.io.StringReader;
import java.util.Iterator;
import java.util.List;

import com.catal.framework.data.Condition;
import com.catal.framework.data.ConditionOperSign;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.select.Distinct;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;

public class OracleSqlPareserUtil {

	/**
	 * sql语句不能带union
	 * 
	 * @param sqlSrc
	 *            源sql
	 * @param offset
	 * @param pagesize
	 * @param condition 查询条件
	 * @param orderField 排序的条件。条件为 "列名  排序方式(ASC|DESC)"
	 * @return
	 * @throws JSQLParserException
	 */
	@SuppressWarnings("unchecked")
	public static String generateQuerySql(String sqlSrc, int offset,
			int pagesize,Condition condition,String[] orderField) throws JSQLParserException {
		if(condition == null){
			condition = new Condition("1", ConditionOperSign.EQUAL, "1");
		}
		CCJSqlParserManager parserManager = new CCJSqlParserManager();
		PlainSelect plainSelect = (PlainSelect) ((Select) parserManager
				.parse(new StringReader(sqlSrc))).getSelectBody();
		Distinct distinct = plainSelect.getDistinct();
		List selectItems = plainSelect.getSelectItems();
		// Table into = plainSelect.getInto();
		FromItem fromItem = plainSelect.getFromItem();
		List joins = plainSelect.getJoins();
		Expression where = plainSelect.getWhere();
		List groupByColumnReferences = plainSelect.getGroupByColumnReferences();
		List orderByElements = plainSelect.getOrderByElements();
		Expression having = plainSelect.getHaving();
		// Limit limit = plainSelect.getLimit();
		// Top top = plainSelect.getTop();

		String sql = "";
		sql = "SELECT rownum rn,";
		sql += ((distinct != null) ? "" + distinct + " " : "");
		// sql += ((top != null)?""+top+" ":"");
		sql += getStringList(selectItems);
		sql += " FROM " + fromItem.toString().replace("AS", "");
		if (joins != null) {
			Iterator it = joins.iterator();
			while (it.hasNext()) {
				Join join = (Join) it.next();
				if (join.isSimple()) {
					sql += ", " + join;
				} else {
					sql += " " + join;
				}
			}
		}
		// sql += getFormatedList(joins, "", false, false);
		sql += ((where != null) ? " WHERE " + where : "");
		//加上自定义查询条件
		sql += ((where != null) ? " and " + condition : (" WHERE " + condition)); 
		sql += getFormatedList(groupByColumnReferences, "GROUP BY");
		sql += ((having != null) ? " HAVING " + having : "");
		sql += orderByToString(orderByElements);
		// sql += ((limit != null) ? limit+"" : "");

		sql = "SELECT * FROM (" + sql + ") t WHERE rn >= " + offset
				+ " and rn <" + (offset + pagesize);
		if(orderField != null && orderField.length >0){
			sql += " ORDER BY ";
			for(String order : orderField){
				sql += order + " ";
			}
		}
		
		return sql;
	}
	
	/**
	 * 将原sql语句解析分拆，加入查询条件
	 * @param sqlSrc
	 * @param condition
	 * @param countColumn
	 * @return
	 * @throws JSQLParserException
	 */
	public static String generateCountSql(String sqlSrc, Condition condition, String countColumn) throws JSQLParserException{
		if(condition == null){
			condition = new Condition("1", ConditionOperSign.EQUAL, "1");
		}
		CCJSqlParserManager parserManager = new CCJSqlParserManager();
		PlainSelect plainSelect = (PlainSelect) ((Select) parserManager
				.parse(new StringReader(sqlSrc))).getSelectBody();
		Distinct distinct = plainSelect.getDistinct();
		List selectItems = plainSelect.getSelectItems();
		// Table into = plainSelect.getInto();
		FromItem fromItem = plainSelect.getFromItem();
		List joins = plainSelect.getJoins();
		Expression where = plainSelect.getWhere();
		List groupByColumnReferences = plainSelect.getGroupByColumnReferences();
		List orderByElements = plainSelect.getOrderByElements();
		Expression having = plainSelect.getHaving();
		// Limit limit = plainSelect.getLimit();
		// Top top = plainSelect.getTop();

		String sql = "SELECT COUNT(" + countColumn +")" ;
		sql += " FROM " + fromItem.toString().replace("AS", "");
		if (joins != null) {
			Iterator it = joins.iterator();
			while (it.hasNext()) {
				Join join = (Join) it.next();
				if (join.isSimple()) {
					sql += ", " + join;
				} else {
					sql += " " + join;
				}
			}
		}
		// sql += getFormatedList(joins, "", false, false);
		sql += ((where != null) ? " WHERE " + where : "");
		//加上自定义查询条件
		sql += ((where != null) ? " and " + condition : (" WHERE " + condition)); 
		sql += getFormatedList(groupByColumnReferences, "GROUP BY");
		sql += ((having != null) ? " HAVING " + having : "");
		sql += orderByToString(orderByElements);
		return sql;
	}

	public static String getStringList(List list) {
		return getStringList(list, true, false);
	}

	public static String getStringList(List list, boolean useComma,
			boolean useBrackets) {
		String ans = "";
		String comma = ",";
		if (!useComma) {
			comma = "";
		}
		if (list != null) {
			if (useBrackets) {
				ans += "(";
			}

			for (int i = 0; i < list.size(); i++) {
				ans += "" + list.get(i)
						+ ((i < list.size() - 1) ? comma + " " : "");
			}

			if (useBrackets) {
				ans += ")";
			}
		}

		return ans;
	}

	public static String orderByToString(List orderByElements) {
		return getFormatedList(orderByElements, "ORDER BY");
	}

	public static String getFormatedList(List list, String expression) {
		return getFormatedList(list, expression, true, false);
	}

	public static String getFormatedList(List list, String expression,
			boolean useComma, boolean useBrackets) {
		String sql = getStringList(list, useComma, useBrackets);

		if (sql.length() > 0) {
			if (expression.length() > 0) {
				sql = " " + expression + " " + sql;
			} else {
				sql = " " + sql;
			}
		}

		return sql;
	}

	public static void main(String[] args) throws JSQLParserException {
		String statement2 = "select dinstinct(t.a,t.b,t.c) from (select a,b,c,d from tab1 where a > 0 and a< (select count(*) from table2)) t  left join (select * from t_card) t2 where t.a = t2.b and t.a is not null and t.b is not null group by t.a,t,b,t.c having t.a > 0 and t.b > 0 order by t.b asc";
		String sql = OracleSqlPareserUtil.generateQuerySql(statement2, 1, 10,new Condition(),null);
//		String sql2 = OracleSqlPareserUtil.generateCountSql(statement2,"t.a is not null ","id");
//		System.out.println(sql2);
	}
}
